package com.lagou.dao;

import com.lagou.bena.Admin;
import com.lagou.bena.Student;
import com.lagou.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;


public class AdminDaoImp implements AdminDao {
    private QueryRunner qr = null;
    /**
     * 实现接口AdminDao抽象方法，实现管理员的登录
     * @param userName
     * @param userPass
     * @return
     */
    @Override
    public Admin AdminLogin(String userName, String userPass) {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "select * from admin where username = ? and userpass = ?";
            // 3.执行sql语句获取admin对象
            return qr.query(sql, new BeanHandler<>(Admin.class), userName, userPass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 实现AdminDao抽象方法，实现所有学生信息的查询
     * @return
     */
    @Override
    public List<Student> showAll() {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "select * from student";
            // 3.执行sql语句
            return qr.query(sql, new BeanListHandler<>(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 实现addStu抽象方法，实现学生信息的添加
     * @return
     */
    @Override
    public int addStu(Student student) {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "insert into student(stuname,sex,birthday,email,info) values(?,?,?,?,?)";
            // 定义一个object类型的数组，用来设置占位符的值
            Object[] obj = {student.getStuName(), student.getSex(), student.getBirthday(), student.getEmail(), student.getInfo()};
            // 3.执行sql语句
            return qr.update(sql, obj);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0; // 添加失败
    }

    /**
     * 实现学生信息的条件查询
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public List<Student> queryStu(Student student) throws SQLException {
        qr = new QueryRunner(DruidUtils.getDataSource());
        // 用于条件sql语句的 and 拼接
        StringBuilder sql = new StringBuilder("select * from student where 1=1 ");

        // 备注查询条件
        if (null != student.getInfo() && !("".equals(student.getInfo().trim()))) {
            sql.append("and info like ?");
            student.setInfo("%" + student.getInfo() + "%");
            return qr.query(sql.toString(), new BeanListHandler<>(Student.class), student.getInfo());
        }
        // 姓名查询条件
        if (null != student.getStuName() && !("".equals(student.getStuName().trim()))) {
            sql.append("and stuName like ?");
            student.setStuName("%" + student.getStuName() + "%");
            return qr.query(sql.toString(), new BeanListHandler<>(Student.class), student.getStuName());
        }
        // 邮箱查询条件
        if (null != student.getEmail() && !("".equals(student.getEmail().trim()))) {
            sql.append("and email like ?");
            student.setEmail("%" + student.getEmail() + "%");
            return qr.query(sql.toString(), new BeanListHandler<>(Student.class), student.getEmail());
        }
        return null;
    }

    /**
     * 实现通过学号查找学生
     * @param id
     * @return
     */
    @Override
    public Student findById(Integer id) {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "select * from student where stuid = ?";
            // 3.执行sql语句并返回查询结果
            return qr.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 修改学生信息
     * @param student
     * @return
     */
    @Override
    public int updateStu(Student student) {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "update student set stuname = ?,sex = ?,birthday = ?," +
                    "email = ?,info = ? where stuid = ?";
            // 3.执行sql语句给占位符赋值并返回执行结果
            return qr.update(sql, student.getStuName(),student.getSex(), student.getBirthday(),
                    student.getEmail(), student.getInfo(), student.getStuID());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0; // 执行修改失败
    }

    /**
     * 删除学生信息
     * @param id
     * @return
     */
    @Override
    public int deleteStu(Integer id) {
        try {
            // 1.获取QueryRunner对象
            qr = new QueryRunner(DruidUtils.getDataSource());
            // 2.编写sql语句
            String sql = "delete from student where stuid = ?";
            // 3.执行sql语句并返回执行结果
            return qr.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
